package com.ygq.exportdbstructure.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * @author emc
 * @version 1.0
 * createTime 2025-10-30
 * description 生成excel工具类
 */
public class ExportDBExcelUtils {


    /**
     * 创建包含表结构的Excel文件
     */
    public static byte[] createExcelFile(List<Map<String, Object>> tableStructure,
                                         String tableName) throws Exception {

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(tableName + "字段详情");

        // 创建样式
        CellStyle headerStyle = createHeaderStyle(workbook);

        int currentRowNum = 0;

        // 创建标题行
        Row headerRow = sheet.createRow(currentRowNum++);
        String[] baseHeaders = {"序号","字段名","字段类型","数据精度","是否主键","是否可空","备注"};

        // 动态添加数据列标题
        for (int i = 0; i < baseHeaders.length; i++) {
            headerRow.createCell(i).setCellValue(baseHeaders[i]);
            headerRow.getCell(i).setCellStyle(headerStyle);
        }
        // 填充每个字段的信息和对应的数据值
        for (Map<String, Object> column : tableStructure) {
            Row row = sheet.createRow(currentRowNum++);
            int i = 0;
            // 序号
            row.createCell(i++).setCellValue(currentRowNum - 1);
            // 字段名
            row.createCell(i++).setCellValue(column.get("COLUMN_NAME").toString());
            // 数据类型* - 内联处理方式
            String typeName = column.get("TYPE_NAME").toString();
            Object sizeObj = column.get("COLUMN_SIZE");
            Object decimalObj = column.get("DECIMAL_DIGITS");

            String detailedType = typeName;
            if (sizeObj != null) {
                int size = ((Number) sizeObj).intValue();
                if (size > 0) {
                    if (decimalObj != null) {
                        int decimal = ((Number) decimalObj).intValue();
                        if (decimal > 0) {
                            detailedType = typeName + "(" + size + "," + decimal + ")";
                        } else {
                            detailedType = typeName + "(" + size + ")";
                        }
                    } else {
                        detailedType = typeName + "(" + size + ")";
                    }
                }
            }
            row.createCell(i++).setCellValue(detailedType);
            // 数据精度
            Object decimalDigits = column.get("DECIMAL_DIGITS");
            if (decimalDigits == null || ((Number) decimalDigits).intValue() == 0) {
                row.createCell(i++).setCellValue((String) null);
            } else {
                row.createCell(i++).setCellValue(((Number) decimalDigits).intValue());
            }
            // 是否主键
            row.createCell(i++).setCellValue(Boolean.TRUE.equals(column.get("IS_PRIMARY_KEY")) ? "Y" : "N");
            // 是否可空
            row.createCell(i++).setCellValue("YES".equals(column.get("IS_NULLABLE")) ? "Y" : "N");
            // 备注
            row.createCell(i++).setCellValue(column.get("REMARKS") != null ?
                    column.get("REMARKS").toString() : "");
        }

        // 自动调整列宽
        for (int i = 0; i < baseHeaders.length; i++) {
            sheet.autoSizeColumn(i);
            // 设置最小列宽
            if (sheet.getColumnWidth(i) < 3000) {
                sheet.setColumnWidth(i, 3000);
            }
        }

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        return outputStream.toByteArray();
    }

    /**
     * 创建主标题样式（用于“表结构”、“样本数据”等大标题）
     */
    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 14);
        font.setColor(IndexedColors.DARK_BLUE.getIndex()); // 深蓝色字体
        style.setFont(font);
        return style;
    }

    /**
     * 创建数据行样式
     */
    private static CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }



    /**
     * 获取表的前N条样本数据
     */
    private List<Map<String, Object>> getSampleData(Connection conn, String schema,
                                                    String tableName, int limit) throws SQLException {
        List<Map<String, Object>> data = new ArrayList<>();

        String fullTableName = (schema != null ? schema + "." : "") + tableName;
        String sql = "SELECT * FROM " + fullTableName + " LIMIT ?";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, limit);

            try (ResultSet rs = pstmt.executeQuery()) {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();

                // 获取列名列表
                List<String> columnNames = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    columnNames.add(metaData.getColumnName(i));
                }

                // 读取数据行
                while (rs.next()) {
                    Map<String, Object> row = new LinkedHashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        Object value = rs.getObject(i);
                        // 处理NULL值
                        row.put(columnNames.get(i-1), value != null ? value : "NULL");
                    }
                    data.add(row);
                }
            }
        }
        return data;
    }
}
